package recycle.repository;

import io.vertx.core.CompositeFuture;
import io.vertx.core.Future;
import io.vertx.core.json.JsonObject;
import io.vertx.mysqlclient.MySQLPool;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import io.vertx.sqlclient.Tuple;
import recycle.pojo.Category;

import java.util.ArrayList;
import java.util.List;

public class CategoryRepository {
    private final MySQLPool client;

    public CategoryRepository(MySQLPool client) {
        this.client = client;
    }

    private Category rowToCategory(Row row) {
        Category category = new Category();
        category.setId(row.getLong("id"));
        category.setName(row.getString("name"));
        return category;
    }

    public Future<Long> insert(Category category) {
        return client.preparedQuery("INSERT INTO category (name) VALUES (?) ")
                .execute(Tuple.of(category.getName()))
                .map(rows -> rows.property(io.vertx.mysqlclient.MySQLClient.LAST_INSERTED_ID));
    }

    public Future<JsonObject> selectList(Category category, Integer pageNum, Integer pageSize) {
        // 参数校验和默认值设置
        if (pageNum == null || pageNum < 1) pageNum = 1;
        if (pageSize == null || pageSize < 1) pageSize = 10;

        // 计算偏移量
        int offset = (pageNum - 1) * pageSize;

        // 构建基础SQL
        StringBuilder sql = new StringBuilder("SELECT * FROM category WHERE 1=1");
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) AS total FROM category WHERE 1=1");

        // 使用List收集参数更灵活
        List<Object> queryParams = new ArrayList<>();
        List<Object> countParams = new ArrayList<>();

        // 添加条件
        if (category.getId() != null && category.getId() != 0) {
            sql.append(" AND id = ?");
            countSql.append(" AND id = ?");
            queryParams.add(category.getId());
            countParams.add(category.getId());
        }

        if (category.getName() != null && !category.getName().isEmpty()) {
            sql.append(" AND name LIKE CONCAT('%', ?, '%')");
            countSql.append(" AND name LIKE CONCAT('%', ?, '%')");
            queryParams.add(category.getName());
            countParams.add(category.getName());
        }


        // 为查询SQL添加分页参数
        sql.append(" LIMIT ? OFFSET ?");
        queryParams.add(pageSize);
        queryParams.add(offset);

        // 转换为Tuple
        Tuple queryTuple = Tuple.from(queryParams.toArray());
        Tuple countTuple = Tuple.from(countParams.toArray());

        // 执行查询和计数
        Future<RowSet<Row>> queryFuture = client.preparedQuery(sql.toString())
                .execute(queryTuple);

        Future<RowSet<Row>> countFuture = client.preparedQuery(countSql.toString())
                .execute(countTuple);

        // 合并结果
        return CompositeFuture.all(queryFuture, countFuture)
                .compose(composite -> {
                    RowSet<Row> rows = composite.resultAt(0);
                    RowSet<Row> countRows = composite.resultAt(1);

                    List<Category> categoryList = new ArrayList<>();
                    for (Row row : rows) {
                        categoryList.add(rowToCategory(row));
                    }

                    long total = countRows.iterator().next().getLong("total");
                    // int totalPages = (int) Math.ceil((double) total / pageSize);

                    // 构建返回结果
                    return Future.succeededFuture(new JsonObject()
                                    .put("records", categoryList)
                                    .put("total", total)
                            // .put("pageNum", pageNum)
                            // .put("pageSize", pageSize)
                            // .put("totalPages", totalPages)
                    );
                });
    }


    public Future<Category> selectById(Long id) {
        return client.preparedQuery("SELECT * FROM category WHERE id = ?")
                .execute(Tuple.of(id))
                .map(rows -> {
                    if (rows.size() == 0) {
                        return null;
                    }
                    Row row = rows.iterator().next();
                    return rowToCategory(row);
                });
    }

    public Future<List<Category>> selectList() {
        return client.preparedQuery("SELECT * FROM category")
                .execute()
                .map(rows -> {
                    List<Category> categories = new ArrayList<>(rows.size());
                    for (Row row : rows) {
                        categories.add(rowToCategory(row));
                    }
                    return categories;
                });
    }

    public Future<Void> updateById(Category category) {
        return client.preparedQuery(" UPDATE category SET name = ? WHERE id = ? ")
                .execute(Tuple.of(category.getName(), category.getId()))
                .mapEmpty();
    }

    public Future<Void> deleteById(Long id) {
        return client.preparedQuery("DELETE FROM category WHERE id = ?")
                .execute(Tuple.of(id))
                .mapEmpty();
    }
}